﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyWeb.Api.Tests
{
    public class SqlHelper
    {
        ///// <summary>
        ///// 分页查询方法，执行原生参数化 SQL 语句并返回指定页码和每页记录数的数据。
        ///// </summary>
        ///// <param name="connectionString">数据库连接字符串</param>
        ///// <param name="sql">原生参数化 SQL 语句</param>
        ///// <param name="pageIndex">要查询的页码，从 1 开始计数</param>
        ///// <param name="pageSize">每页的记录数</param>
        ///// <param name="parameters">SQL 参数列表</param>
        ///// <returns>包含查询结果的 DataTable 对象</returns>
        //public DataTable QueryPaged(string connectionString, string sql, int pageIndex, int pageSize, Dictionary<string, object> myDictionary, out int totalCount)
        //{
        //    List<SugarParameter> parameters = new List<SugarParameter>();
        //    foreach (KeyValuePair<string, object> kvp in myDictionary)
        //    {
        //        string ss = kvp.Key.ToString();
        //        parameters.Add(new SugarParameter(kvp.Key, kvp.Value));
        //    }
        //    using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
        //    {
        //        ConnectionString = connectionString,
        //        DbType = DbType.SqlServer,
        //        IsAutoCloseConnection = true
        //    }))
        //    {
        //        db.Ado.CommandTimeOut = 120; // 设置超时时间为 120 秒
        //        totalCount = db.Ado.GetInt($"SELECT COUNT(1) FROM ({sql}) t", parameters?.ToArray());
        //        if (totalCount == 0)
        //        {
        //            return null;
        //        }
        //        int pageCount = (int)Math.Ceiling(totalCount * 1.0 / pageSize);
        //        if (pageIndex < 1)
        //        {
        //            pageIndex = 1;
        //        }
        //        else if (pageIndex > pageCount)
        //        {
        //            pageIndex = pageCount;
        //        }

        //        StringBuilder querySql = new StringBuilder();
        //        querySql.Append("WITH SortedData AS (");
        //        querySql.Append(sql);
        //        querySql.Append("), NumberedData AS (");
        //        querySql.Append("SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM SortedData");
        //        querySql.Append(") SELECT * FROM NumberedData WHERE RowNum BETWEEN @start AND @end");

        //        parameters ??= new List<SugarParameter>();
        //        parameters.Add(new SugarParameter("@start", (pageIndex - 1) * pageSize + 1));
        //        parameters.Add(new SugarParameter("@end", pageIndex * pageSize));

        //        DataTable dt = db.Ado.GetDataTable(querySql.ToString(), parameters.ToArray());

        //        return dt;
        //    }
        //}
    }
}
